import pandas as pd

url = 'http://72.itmc.org.cn:80/JS001/data/user/15108/242/fj_employee_salary_work_books.xlsx'
data01 = pd.read_excel(url, sheet_name='基本薪资')
data02 = pd.read_excel(url, sheet_name='上班通勤')
data = pd.merge(data01, data02)
data = data.fillna(0)
data['月基本薪资'] = data['基本薪资'] + data['岗位工资'] + data['绩效工资']
#         print(data.info())
data['日薪'] = data['月基本薪资'] / data['应出勤天数（天）']
data['时薪'] = data['日薪'] / 8
#         print(data['时薪'])
# data['月通勤工资'] = data['时薪'] * (data['工作日加班（小时）'] + data['法定假日加班（小时）'] * 2 + data['周末加班（小时）'] * 1.5)
data['月通勤工资'] = data['时薪'] * data['工作日加班（小时）'] + \
                data['时薪'] * data['法定假日加班（小时）'] * 2 + \
                data['时薪'] * data['周末加班（小时）'] * 1.5
data['月通勤工资'] = data['月通勤工资']
#         print(data['月通勤工资'])
data['月五险一金'] = (data['社会保险缴费基数'] * (0.08 + 0.02 + 0.01 + 0.1))
#         print(data['月五险一金'])
data['月请假扣除'] = data['请假（小时）'] * data['时薪']
data['月请假扣除'] = data['月请假扣除']
#         print(data['月请假扣除'])
data['月应发工资'] = data['月基本薪资'] + data['月通勤工资'] - data['月五险一金'] - data['月请假扣除']
data['月应发工资'] = data['月应发工资']
avg_xinz = data.groupby('部门')['月应发工资'].mean().round(2).sort_values(ascending=False)
avg_xinz.name = '平均薪资'
print(avg_xinz)
